/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db.JDBC;

import core.Book;
import core.Course;
import db.interfaces.BookDAO;
import java.util.Collection;
import java.sql.Connection;

/**
 *
 * @author Anton
 */
public class BookJdbcDAO extends BaseJdbcDAO implements BookDAO
{
    /**
     * 
     * @param con
     */
    public BookJdbcDAO(Connection con)
    {
        super(con);
    }

    /**
     * 
     * @param id
     * @return
     */
    @Override
    public Book getBookById(long id)
    {
        return super.getBookById(id);
    }

    /**
     * 
     * @param ISBN10
     * @return
     */
    public Book getBookByISBN10(String ISBN10) 
    {
        if(ISBN10 == null)
            return null;
        String sql = "SELECT * FROM Books WHERE isbn10='" + ISBN10 + "';";
        return createBook(sqlExecuteQuery(sql));
    }

    /**
     *
     * @param ISBN13
     * @return
     */
    public Book getBookByISBN13(String ISBN13)
    {
        if(ISBN13 == null)
            return null;
        String sql = "SELECT * FROM Books WHERE isbn13='" + ISBN13 + "';";
        return createBook(sqlExecuteQuery(sql));
    }

    public Collection<Book> searchForBooksByTitle(String beginsWith)
    {
        return search(beginsWith, "title");
    }
    /**
     *
     * @param beginsWith
     * @return
     */
    public Collection<Book> searchForBooksByAuthor(String beginsWith)
    {
        return search(beginsWith, "author");
    }
    /**
     *
     * @param beginsWith
     * @return
     */
    public Collection<Book> searchForBooksByISBN10(String beginsWith)
    {
        return search(beginsWith, "isbn10");
    }
    /**
     *
     * @param beginsWith
     * @return
     */
    public Collection<Book> searchForBooksByISBN13(String beginsWith)
    {
        return search(beginsWith, "isbn13");
    }
    /**
     *
     * @param beginsWith
     * @param fieldName
     * @param asc
     * @return
     */
    public Collection<Book> searchForBooksByParameters(String beginsWith, String fieldName, boolean asc)
    {
        String sql = "";
        String sort = "ASC";
        if(!asc)
            sort = "DESC";
        if(fieldName.equals(""))
            sql = "SELECT * FROM Books ORDER BY " + fieldName + " " + sort +";";
        else
            sql = "SELECT * FROM Books WHERE " +
                "id LIKE '" + beginsWith + "%' OR " +
                "title LIKE '" + beginsWith + "%' OR " +
                "author LIKE '" + beginsWith + "%' OR " +
                "isbn10 LIKE '" + beginsWith + "%' OR " +
                "isbn13 LIKE '" + beginsWith + "%' ORDER BY " +
                fieldName + " " + sort +";";
        return createBookCollection(sqlExecuteQuery(sql));
    }
    /**
     *
     * @param beginsWith
     * @param field
     * @return
     */
    private Collection<Book> search(String beginsWith, String field)
    {
        String sql = "SELECT * FROM Books WHERE " + field + " LIKE '" + beginsWith + "%' ORDER BY " + field + " ASC;";
        return createBookCollection(sqlExecuteQuery(sql));
    }

    /**
     * 
     * @param author
     * @return
     */
    public Collection<Book> getBooksByAuthor(String author)
    {
        if(author == null)
            return null;
        String sql = "SELECT * FROM Books WHERE author='" + author + "' ORDER BY title ASC;";
        return createBookCollection(sqlExecuteQuery(sql));
    }

    /**
     * 
     * @param title
     * @return
     */
    public Collection<Book> getBooksByTitle(String title)
    {
        if(title == null)
            return null;
        String sql = "SELECT * FROM Books WHERE title='" + title + "' ORDER BY author ASC;";
        return createBookCollection(sqlExecuteQuery(sql));
    }

    /**
     * 
     * @return
     */
    public Collection<Book> getAllBooks()
    {
        String sql = "SELECT * FROM Books ORDER BY title ASC, author ASC;";
        return createBookCollection(sqlExecuteQuery(sql));        
    }

    /**
     * 
     * @param course
     * @return
     */
    public Collection<Book> getAllBooksInCourse(Course course)
    {
        if(course == null)
            return null;
        String sql = "SELECT * FROM CourseBook WHERE course='" + course.getId() + "';";
        return createBookCollection(createIdCollection(sqlExecuteQuery(sql), "book"));
    }

    /**
     * 
     * @param book
     * @return
     */
    @Override
    public boolean bookIsBound(Book book)
    {
        if(book == null)
            return false;
        return super.bookIsBound(book);
    }
    /**
     *
     * @param book
     * @return
     */
    public boolean containsBook(Book book)
    {
        if(book == null)
            return false;
        String sql = "SELECT * FROM Books WHERE id='" + book.getId() + "';";
        return contains(sql);
    }
    /**
     * 
     * @param book
     * @return
     */
    public boolean addBook(Book book)
    {
        if(book == null)
            return false;
        String sql = "INSERT INTO Books (title, author, description, price, weblink, isbn10, isbn13) " +
                           "values ("
                           + "'" + book.getTitle() + "', "
                           + "'" +  book.getAuthor() + "', "
                           + "'" +  book.getDescription() + "', "
                           + "'" +  book.getPrice() + "', "
                           + "'" +  book.getLink() + "', "
                           + "'" +  book.getIsbn10() + "', "
                           + "'" +  book.getIsbn13() + "');";
        boolean b = add(sql);
        book.setId(getLatestAddedId("Books"));
        return b;
    }
    /**
     * 
     * @param book
     * @return
     */
    public boolean editBook(Book book)
    {
        if(book == null)
            return false;
        String sql = "UPDATE Books SET  " +
                "title='" + book.getTitle() +
                "', author = '" + book.getAuthor() +
                "', description = '" + book.getDescription() +
                "', price = '" + book.getPrice() +
                "', weblink = '" + book.getLink() +
                "', isbn10 = '" + book.getIsbn10() +
                "', isbn13 = '" + book.getIsbn13()+
                "' WHERE id='" + book.getId() + "';";
        return edit(sql);
    }
    /**
     * 
     * @param book
     * @return
     */
    public boolean deleteBook(Book book)
    {
        if(book == null)
            return false;
        deleteBookFromAllBindings(book);
        String sql = "DELETE FROM Books WHERE id='" + book.getId() + "';";
        return delete(sql);
    }
    /**
     * 
     * @param book
     */
    private void deleteBookFromAllBindings(Book book)
    {
        if(book == null)
            return;
        String sql = "DELETE FROM CourseBook WHERE book = ' " + book.getId() + "'";
        delete(sql);
    }
}
